package es.maria;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import javax.sql.*;
import javax.naming.*;

public class ModeloBD {
    Connection connection;
    private boolean connectionFree = true;
    private ArrayList<Tiempo> listaTiempo;
    private ArrayList<ComunidadAutonoma> listaComunidades;
    private ArrayList<Prediccion> listaPredicciones;
    
    public ModeloBD () throws Exception {
        try {
            InitialContext initialContext = new InitialContext ();
            Context envContext = (Context) initialContext.lookup ("java:comp/env");
            DataSource dataSource = (DataSource) envContext.lookup ("jdbc/Meteorologia");
            this.connection = dataSource.getConnection ();
        }
        catch (Exception e) {
            throw new Exception ("No se pudo abrir la base de datos de meteorologia " + e.getMessage ());
        }
    }

    protected synchronized Connection getConnection () {
        while (this.connectionFree == false) {
            try {
                wait ();
            }
            catch (InterruptedException e) {
            }
        }
        this.connectionFree = false;
        notify ();
        return this.connection;
    }

    protected synchronized void releaseConnection () {
        while (this.connectionFree == true) {
            try {
                wait ();
            }
            catch (InterruptedException e) {
            }
        }
        this.connectionFree = true;
        notify ();
    }
    
    public String getNombreComunidad (int comunidad) {
    	String nombreComunidad ="";
        try {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement 
            		("SELECT nombre FROM comunidades_autonomas WHERE id = ?");
            
            preparedStatement.setInt(1, comunidad );
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ()) {
            	nombreComunidad = 
                    resultSet.getString (1);
                
                preparedStatement.close ();
                this.releaseConnection ();
                return nombreComunidad;
            }
            else {
                preparedStatement.close ();
                this.releaseConnection ();
                return null;
            }
        }
        catch (SQLException e) {
            this.releaseConnection ();
            return null;
        }
    }

    public Tiempo getMeteorologia (Date fecha, int comunidad) {
    	
    	
        try {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement 
            		("SELECT fecha, comunidad, prediccion, minimo, maximo, viento, c.nombre, p.nombre "
            				+ "FROM tiempo t, predicciones p, comunidades_autonomas c "
            				+ "WHERE  p.id = t.prediccion and c.id = t.comunidad "
            				+ "and comunidad = ? and fecha = ?");           
            preparedStatement.setInt(1, comunidad );
            preparedStatement.setString(2, fecha.toString());
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ()) {            	
            	Tiempo tiempo = new Tiempo(
            			resultSet.getDate (1), resultSet.getInt (2), resultSet.getInt (3),  resultSet.getFloat(4), resultSet.getFloat (5),
            			resultSet.getFloat(6), resultSet.getString(7),resultSet.getString(8));
                preparedStatement.close ();
                this.releaseConnection ();
                return tiempo;
            }
            else {
                preparedStatement.close ();
                this.releaseConnection ();
                return null;
            }
        }
        catch (SQLException e) {
            this.releaseConnection ();
            return null;
        }
        
    	
    	
    }

    public int insertarMeteorologia (Tiempo meteorologia) {
        int rowsAffected = 0;
        try {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement 
                 ("INSERT INTO tiempo (fecha, comunidad, prediccion, minimo, maximo, viento) VALUES (?, ?, ?, ?, ?,?)");
            preparedStatement.setDate (1, meteorologia.getFecha());
            preparedStatement.setInt(2, meteorologia.getComunidad());
            preparedStatement.setInt (3, meteorologia.getPrediccion());
            preparedStatement.setFloat (4, meteorologia.getMinimo());
            preparedStatement.setFloat (5, meteorologia.getMaximo());
            preparedStatement.setFloat (6, meteorologia.getViento());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            this.releaseConnection ();
        }
        catch (SQLException e) {
            this.releaseConnection ();
            return 0;
        }
        return rowsAffected;
    }

	public int borrarMeteorologia (Date fecha, int comunidad){
        int rowsAffected = 0;
        try {
            this.getConnection ();
            PreparedStatement preparedStatement = 
                this.connection.prepareStatement ("DELETE FROM tiempo WHERE  comunidad = ? and fecha = ? ");
            preparedStatement.setInt (1, comunidad);
            preparedStatement.setString(2, fecha.toString());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            this.releaseConnection ();
        }
        catch (SQLException e) {
            this.releaseConnection ();
            return 0;
        }
        return rowsAffected;
    }

    public int modificarMeteorologia (Tiempo meteorologia) {
        int rowsAffected = 0;
        try {
            this.getConnection ();
            PreparedStatement preparedStatement = 
              this.connection.prepareStatement ("UPDATE tiempo SET ,"
              		+ "prediccion=?, minimo=?, maximo=?, viento=? WHERE fecha=? and comunidad=?");
            preparedStatement.setInt (1, meteorologia.getPrediccion());
            preparedStatement.setFloat (2, meteorologia.getMinimo());
            preparedStatement.setFloat (3, meteorologia.getMaximo());
            preparedStatement.setFloat (4, meteorologia.getViento());   	
           
           
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            this.releaseConnection ();
        }
        catch (SQLException e) {
            this.releaseConnection ();
            return 0;
        }
        return rowsAffected;
    }

    public ArrayList<Tiempo> getMeteorologias () throws SQLException {
    	listaTiempo = new ArrayList<Tiempo> ();
       // try {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement 
            		 ("SELECT fecha, comunidad, prediccion, minimo, maximo, viento , p.nombre, c.nombre "
            		 		+ "FROM tiempo t, predicciones p, comunidades_autonomas c "
            		 		+ "WHERE t.prediccion = p.id and t.comunidad = c.id");
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ()) {
            	Tiempo meteorologia = new Tiempo (
                        resultSet.getDate (1), resultSet.getInt (2), resultSet.getInt (3),
                        resultSet.getFloat(4), resultSet.getFloat (5), resultSet.getFloat(6), resultSet.getString(7),resultSet.getString(8));
                listaTiempo.add (meteorologia);
            }
            preparedStatement.close ();
       // }
       // catch (SQLException e) {
       // 	System.err.println("An exception occurred: " + e.getMessage());
       //     return null;
         
       // }
        this.releaseConnection ();

        return listaTiempo;
    }
    
    public ArrayList<ComunidadAutonoma> getAutonomias() throws SQLException {
    	listaComunidades = new ArrayList<ComunidadAutonoma> ();
       // try {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement 
            		 ("SELECT id, nombre from  comunidades_autonomas");
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ()) {
            	ComunidadAutonoma comunidadAutonoma = new ComunidadAutonoma (
                        resultSet.getInt(1), resultSet.getString (2));
            	listaComunidades.add (comunidadAutonoma);
            }
            preparedStatement.close ();
       // }
       // catch (SQLException e) {
       // 	System.err.println("An exception occurred: " + e.getMessage());
       //     return null;
         
       // }
        this.releaseConnection ();

        return listaComunidades;
    }
    
    public ArrayList<Prediccion> getPredicciones() throws SQLException {
    	listaPredicciones = new ArrayList<Prediccion> ();
       // try {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement 
            		 ("SELECT id, nombre from  predicciones");
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ()) {
            	Prediccion prediccion = new Prediccion (
                        resultSet.getInt(1), resultSet.getString (2));
            	listaPredicciones.add (prediccion);
            }
            preparedStatement.close ();
       // }
       // catch (SQLException e) {
       // 	System.err.println("An exception occurred: " + e.getMessage());
       //     return null;
         
       // }
        this.releaseConnection ();

        return listaPredicciones;
    }

    public void close () {
        try {
            this.connection.close ();
        }
        catch (SQLException e) {
            System.out.println (e.getMessage ());
        }
    }

}
